const db = require("../model/database");
const sqlQuery = require("../model/query");
const dayjs = require("dayjs");

// 查询考试批次
exports.getExamBatch = async (req, res) => {
    const sql = "SELECT * FROM test";
    const result = await sqlQuery(sql, null, db);

    if (result.length > 0) {
        res.json({
            code: 20000,
            data: result
        });
    } else {
        res.json({
            code: 20000,
            msg: "暂无数据"
        });
    }
};

// 查询考试科目及成绩
exports.getExamSubject = async (req, res) => {
    // 获取参数 分别是考试批次和班级
    const { testId, classId, type } = req.body;
    // 由于可能出现新增学生的情况, 所以这里需要先查询出所有学生, 然后再查询成绩
    // 查询成绩表中该班级和该考试批次的学生id
    const sql1 =
        "SELECT student_id FROM course WHERE test_id = ? AND student_id IN (SELECT id FROM student WHERE class = ?)";
    const result1 = await sqlQuery(sql1, [testId, classId], db);
    // 查询该班级的所有学生id
    const sql2 = "SELECT id FROM student WHERE class = ?";
    const result2 = await sqlQuery(sql2, [classId], db);
    // 从result1和result2中取出值
    const result1Id = result1.map((item) => item.student_id);
    const result2Id = result2.map((item) => item.id);
    // 比较两个数组, 找出差集
    const diff = result2Id.filter((item) => !result1Id.includes(item));
    // 将差集插入到成绩表中
    if (diff.length > 0) {
        const sql3 = "INSERT INTO course (test_id, student_id) VALUES ?";
        const values = [];
        diff.forEach((item) => {
            values.push([testId, item]);
        });
        await sqlQuery(sql3, [values], db);
    }

    // 由于设计三个表course test student, 所以这里使用多表查询
    // type 0: 查询成绩 1: 成绩录入
    let sql = "";
    if (type === 0) {
        sql =
            "SELECT * FROM course c, test t, student s WHERE c.test_id = t.id AND c.student_id = s.id AND c.test_id = ? AND s.class = ? And s.is_delete = 0";
    } else if (type === 1) {
        sql =
            "SELECT * FROM course c, test t, student s WHERE c.test_id = t.id AND c.student_id = s.id AND c.test_id = ? AND s.class = ? And s.is_delete = 0";
    }
    const result = await sqlQuery(sql, [testId, classId], db);

    res.json({
        code: 20000,
        data: result
    });
};

// 新增考试批次
exports.addExamBatch = async (req, res) => {
    // 获取参数 考试名称 备注
    const { title, note } = req.body;
    // 插入数据库
    const sql = "INSERT INTO test (title, note, create_time) VALUES (?, ?, ?)";
    const create_time = dayjs().format("YYYY-MM-DD HH:mm:ss");
    const result = await sqlQuery(sql, [title, note, create_time], db);

    // 新增的同时 创建相应的学生成绩记录
    // 1. 查询所有学生
    const sql1 = "SELECT * FROM student";
    const result1 = await sqlQuery(sql1, null, db);
    // 2. 插入成绩记录
    const sql2 = "INSERT INTO course (student_id, test_id) VALUES (?, ?)";
    for (let i = 0; i < result1.length; i++) {
        await sqlQuery(sql2, [result1[i].id, result.insertId], db);
    }

    if (result.affectedRows > 0) {
        res.json({
            code: 20000,
            msg: "新增成功"
        });
    } else {
        res.json({
            code: 20000,
            msg: "新增失败"
        });
    }
};

// 批量录入成绩
exports.addScore = async (req, res) => {
    // 获取参数
    const data = req.body;
    // 更新数据库 各科成绩 总分
    const sql =
        "UPDATE course SET daily_score = ?, work_score = ?, mid_score = ?, final_score = ?, average = ? WHERE student_id = ? And test_id = ?";
    for (let i = 0; i < data.length; i++) {
        const { daily_score, work_score, mid_score, final_score, average, student_id, test_id } = data[i];
        await sqlQuery(sql, [daily_score, work_score, mid_score, final_score, average, student_id, test_id], db);
    }

    res.json({
        code: 20000,
        msg: "录入成功"
    });
};

// 修改考试
exports.updateExam = async (req, res) => {
    // 获取参数
    const { id, title, note } = req.body;
    // 更新数据库
    const sql = "UPDATE test SET title = ?, note = ? WHERE id = ?";
    const result = await sqlQuery(sql, [title, note, id], db);

    if (result.affectedRows > 0) {
        res.json({
            code: 20000,
            msg: "修改成功"
        });
    } else {
        res.json({
            code: 20000,
            msg: "修改失败"
        });
    }
};

// 删除考试
exports.deleteExam = async (req, res) => {
    // 获取参数
    const { id } = req.body;

    // 由于外键约束, 需要先删除course表中的数据
    const sql1 = "DELETE FROM course WHERE test_id = ?";
    await sqlQuery(sql1, [id], db);

    // 删除数据库
    const sql = "DELETE FROM test WHERE id = ?";
    const result = await sqlQuery(sql, [id], db);

    if (result.affectedRows > 0) {
        res.json({
            code: 20000,
            msg: "删除成功"
        });
    } else {
        res.json({
            code: 20000,
            msg: "删除失败"
        });
    }
};

// 查询学生的考试成绩
exports.getStudentScore = async (req, res) => {
    // 获取参数
    const { id } = req.params;
    // 查询最新考试批次的成绩
    const sql =
        "SELECT * FROM course c, test t WHERE c.test_id = t.id AND c.student_id = ? ORDER BY t.create_time DESC LIMIT 1";
    const result = await sqlQuery(sql, [id], db);
    // 生成数据
    const data = [
        {
            id: 1,
            name: "平时成绩",
            score: result[0]?.daily_score || 0
        },
        {
            id: 2,
            name: "作业成绩",
            score: result[0]?.work_score || 0
        },
        {
            id: 3,
            name: "期中考试成绩",
            score: result[0]?.mid_score || 0
        },
        {
            id: 4,
            name: "期末考试成绩",
            score: result[0]?.final_score || 0
        },
        {
            id: 5,
            name: "平均分",
            score: result[0]?.average || 0
        }
    ];
    res.json({
        code: 20000,
        data
    });
};
